package org.omlite.domain.db;

import java.io.Serializable;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.omlite.domain.models.AbsenceModel;
import org.omlite.domain.models.AdminModel;
import org.omlite.domain.models.ContactsModel;
import org.omlite.domain.models.DatesModel;
import org.omlite.domain.models.InviteesModel;
import org.omlite.domain.models.ProjectModel;
import org.omlite.domain.models.SubscriptionsModel;

/**
 *
 * @author peterhendriks
 */
public class OrkestManagerDB implements Serializable
{
    private static final Logger log = Logger.getLogger(OrkestManagerDB.class.getName());

    private final SQLiteConnector db;
    private StringBuilder sql;
    private ResultSet rs;

    public OrkestManagerDB(String dbFileName, boolean consoleOutput)
    {
        db = new SQLiteConnector(dbFileName);
        db.setShowConsoleOutput(consoleOutput);
    }

    public int getContactID(ContactsModel c) throws Exception
    {
        sql = new StringBuilder("SELECT id FROM tbl_contacts WHERE firstName = '");
        sql.append(c.getFirstName());
        sql.append("' AND lastName = '");
        sql.append(c.getLastName());
        sql.append("' AND email = '");
        sql.append(c.getEmail());
        sql.append("' AND instrument = '");
        sql.append(c.getInstrument());
        sql.append("';");
        rs = db.ExecuteSelectStatement(sql.toString());
        int i = 0;

        while (rs.next())
        {
            i = rs.getInt("id");
        }
        return i;
    }

    public ArrayList getAllProjectInfo(String column)
    {
        sql = new StringBuilder("SELECT ");
        sql.append(column);
        sql.append(" FROM tbl_projects WHERE active = 'true' ORDER BY projectStartDate DESC;");
        rs = db.ExecuteSelectStatement(sql.toString());
        ArrayList buf = new ArrayList();
        try
        {
            while (rs.next())
            {
                buf.add(rs.getString(column));
            }
        } catch (Exception e)
        {
            buf.add(e.getMessage());
            log.log(Level.SEVERE, e.getMessage());
        }
        return buf;
    }

    public String getProjectInfo(String column, String projectName)
    {
        sql = new StringBuilder("SELECT ");
        sql.append(column);
        sql.append(" FROM tbl_projects WHERE projectName = '");
        sql.append(projectName);
        sql.append("';");
        rs = db.ExecuteSelectStatement(sql.toString());
        String buf = null;
        try
        {
            while (rs.next())
            {
                buf = rs.getString(column);
            }
        } catch (Exception e)
        {
            buf = e.getMessage();
            log.log(Level.SEVERE, e.getMessage());
        }
        db.Disconnect();
        return buf;
    }

    public String getTextLabel(String labelName)
    {
        sql = new StringBuilder("SELECT value FROM tbl_textLabels WHERE name = '");
        sql.append(labelName);
        sql.append("';");
        rs = db.ExecuteSelectStatement(sql.toString());
        String buf = null;
        try
        {
            while (rs.next())
            {
                String s = rs.getString("value");
                if (labelName.equalsIgnoreCase("invitationMSG"))
                {
                    buf = s;
                } else
                {
                    buf = s.replace("#!#", "'");
                }
            }
        } catch (Exception e)
        {
            buf = e.getMessage();
            log.log(Level.SEVERE, e.getMessage());
        }
        return buf;
    }

    public int getProjectID(String projectName)
    {
        sql = new StringBuilder("SELECT id FROM tbl_projects WHERE projectName = '");
        sql.append(projectName);
        sql.append("';");
        rs = db.ExecuteSelectStatement(sql.toString());
        int buf = 0;
        try
        {
            while (rs.next())
            {
                buf = rs.getInt("id");
            }
        } catch (Exception e)
        {
            log.log(Level.SEVERE, e.getMessage());
        }
        return buf;
    }

    public ArrayList getDates(int projectID, int contactID)
    {
        ArrayList<DatesModel> datesModel = new ArrayList();
        sql = new StringBuilder("SELECT * FROM tbl_dates WHERE projectID = ");
        sql.append(projectID);
        sql.append(" AND contactID = ");
        sql.append(contactID);
        sql.append(" ORDER BY date ASC;");
        rs = db.ExecuteSelectStatement(sql.toString());
        try
        {
            while (rs.next())
            {
                datesModel.add(new DatesModel(rs.getInt("projectID"),
                        rs.getInt("contactID"), rs.getString("date"),
                        rs.getString("startTime"), rs.getString("endTime"),
                        rs.getString("location"), rs.getString("dateInfo"),
                        rs.getString("commentsByContact").replace("#!#", "'"),
                        Boolean.parseBoolean(rs.getString("present"))));
            }
        } catch (Exception e)
        {
            log.log(Level.SEVERE, e.getMessage());
        }
        return datesModel;
    }

    public boolean contactExists(String email)
    {
        sql = new StringBuilder("SELECT email FROM tbl_contacts WHERE email ='");
        sql.append(email);
        sql.append("';");
        rs = db.ExecuteSelectStatement(sql.toString());
        boolean addressExists = false;
        try
        {
            while (rs.next())
            {
                if (rs.getString("email").equals(email))
                {
                    addressExists = true;
                }
            }
        } catch (Exception e)
        {
            log.log(Level.SEVERE, e.getMessage());
        }
        return addressExists;
    }

    public boolean contactExistInProject(int contactID, int projectID)
    {
        sql = new StringBuilder("SELECT contactID FROM tbl_dates WHERE projectID = ");
        sql.append(projectID);
        sql.append(" AND contactID = ");
        sql.append(contactID);
        sql.append(";");
        rs = db.ExecuteSelectStatement(sql.toString());
        boolean contactInProject = false;
        try
        {
            while (rs.next())
            {
                if (rs.getInt("contactID") == contactID)
                {
                    contactInProject = true;
                }
            }
        } catch (Exception e)
        {
            log.log(Level.SEVERE, e.getMessage());
        }
        return contactInProject;
    }

    public boolean SaveNewMember(int projectID, String contactInstrument, String contactFirstName, String contactLastName, String contactEmail, String contactPhone, ArrayList<DatesModel> datesModel)
    {
        try
        {
            //eerst contact inserten
            sql = new StringBuilder("INSERT INTO tbl_contacts (instrument, firstName, lastName, email, phone) VALUES ('");
            sql.append(contactInstrument);
            sql.append("','");
            sql.append(contactFirstName);
            sql.append("','");
            sql.append(contactLastName);
            sql.append("','");
            sql.append(contactEmail);
            sql.append("','");
            sql.append(contactPhone);
            sql.append("');");
            db.ExecuteInsertStatement(sql.toString());

            //dan contactID opvragen
            sql = new StringBuilder("SELECT id FROM tbl_contacts WHERE email = '");
            sql.append(contactEmail);
            sql.append("';");
            int contactID = 0;
            rs = db.ExecuteSelectStatement(sql.toString());
            while (rs.next())
            {
                contactID = rs.getInt("id");
            }
            if (contactID != 0)
            {
                //dan dates toevoegen, itereren door arraylist!!
                for (DatesModel date : datesModel)
                {
                    sql = new StringBuilder("INSERT INTO tbl_dates (projectID, contactID, date, startTime, endTime, location, dateInfo, commentsByContact, present) VALUES (");
                    sql.append(projectID);
                    sql.append(",");
                    sql.append(contactID);
                    sql.append(",'");
                    sql.append(date.getDate());
                    sql.append("','");
                    sql.append(date.getStartTime());
                    sql.append("','");
                    sql.append(date.getEndTime());
                    sql.append("','");
                    sql.append(date.getLocation());
                    sql.append("','");
                    sql.append(date.getDateInfo());
                    sql.append("','");
                    sql.append(date.getCommentsByContact().replace("'", "#!#"));
                    sql.append("','");
                    sql.append(date.isPresent());
                    sql.append("');");
                    db.ExecuteInsertStatement(sql.toString());
                }
            } else
            {
                log.log(Level.WARNING, "Probleem: ContactID = 0!");
                return false;
            }
            db.Disconnect();
        } catch (Exception e)
        {
            log.log(Level.SEVERE, e.getMessage());
            db.Disconnect();
            return false;
        }
        return true;
    }

    public boolean SaveExistingMemberAndDates(int contactID,
            int projectID,
            String contactInstrument,
            String contactFirstName,
            String contactLastName,
            String contactEmail,
            String contactPhone,
            ArrayList<DatesModel> datesModel)
    {
        try
        {
            sql = new StringBuilder("UPDATE tbl_contacts SET instrument = '");
            sql.append(contactInstrument);
            sql.append("', firstName = '");
            sql.append(contactFirstName);
            sql.append("', lastName = '");
            sql.append(contactLastName);
            sql.append("', email = '");
            sql.append(contactEmail);
            sql.append("', phone = '");
            sql.append(contactPhone);
            sql.append("' WHERE ");
            sql.append("id = ");
            sql.append(contactID);
            sql.append(";");
            db.ExecuteUpdateStatement(sql.toString());

            if (contactID != 0)
            {
                if (!contactExistInProject(contactID, projectID))
                {
                    ArrayList<DatesModel> masterDates = this.getDates(projectID, 0);
                    for (DatesModel masterDate : masterDates)
                    {
                        sql = new StringBuilder("INSERT into tbl_dates (projectID, contactID, date, startTime, endTime, present, commentsByContact, location, dateInfo) VALUES (");
                        sql.append(projectID);
                        sql.append(", ");
                        sql.append(contactID);
                        sql.append(", '");
                        sql.append(masterDate.getDate());
                        sql.append("', '");
                        sql.append(masterDate.getStartTime());
                        sql.append("', '");
                        sql.append(masterDate.getEndTime());
                        sql.append("', '");
                        sql.append(masterDate.isPresent());
                        sql.append("', '");
                        sql.append(masterDate.getCommentsByContact().replace("'", "#!#"));
                        sql.append("', '");
                        sql.append(masterDate.getLocation());
                        sql.append("', '");
                        sql.append(masterDate.getDateInfo());
                        sql.append("');");
                        db.ExecuteInsertStatement(sql.toString());
                    }
                }
                //dan dates updaten, itereren door arraylist!!
                for (DatesModel date : datesModel)
                {
                    sql = new StringBuilder("UPDATE tbl_dates SET commentsByContact = '");
                    sql.append(date.getCommentsByContact().replace("'", "#!#"));
                    sql.append("', present = '");
                    sql.append(date.isPresent());
                    sql.append("' WHERE contactID = ");
                    sql.append(contactID);
                    sql.append(" AND date = '");
                    sql.append(date.getDate());
                    sql.append("' AND startTime = '");
                    sql.append(date.getStartTime());
                    sql.append("';");
                    db.ExecuteUpdateStatement(sql.toString());
                }
            } else
            {
                log.log(Level.WARNING, "ContactID = 0! dus datums worden niet gewijzigd!");
                return false;
            }
            db.Disconnect();
        } catch (Exception e)
        {
            log.log(Level.SEVERE, e.getMessage());
            db.Disconnect();
            return false;
        }
        return true;
    }

    public ArrayList getAllContactsFullNames()
    {
        ArrayList fullNames = new ArrayList();
        sql = new StringBuilder("SELECT firstName, lastName FROM tbl_contacts ORDER BY firstName;");
        rs = db.ExecuteSelectStatement(sql.toString());
        try
        {
            while (rs.next())
            {
                StringBuilder buf = new StringBuilder();
                buf.append(rs.getString("firstName"));
                buf.append(" ");
                buf.append(rs.getString("lastName"));
                fullNames.add(buf.toString());
            }
        } catch (Exception e)
        {
            log.log(Level.SEVERE, e.getMessage());
        }
        return fullNames;
    }

    public ArrayList getContactInfoByFullName(String fullName)
    {
        ArrayList contacts = new ArrayList();
        sql = new StringBuilder("SELECT * FROM tbl_contacts WHERE firstName = '");
        sql.append(fullName.substring(0, fullName.indexOf(" ")).replace("_", " "));
        sql.append("' AND lastName = '");
        sql.append(fullName.substring(fullName.indexOf(" ") + 1, fullName.length()).replace("_", " "));
        sql.append("';");
        rs = db.ExecuteSelectStatement(sql.toString());
        try
        {
            while (rs.next())
            {
                contacts.add(rs.getInt("id"));
                contacts.add(rs.getString("instrument"));
                contacts.add(rs.getString("firstName"));
                contacts.add(rs.getString("lastName"));
                contacts.add(rs.getString("email"));
                contacts.add(rs.getString("phone"));
            }
        } catch (Exception e)
        {
            log.log(Level.SEVERE, e.getMessage());
        }
        return contacts;
    }

    public ArrayList getAllContactsToInvite()
    {
        ArrayList<InviteesModel> inviteesModel = new ArrayList();
        sql = new StringBuilder("SELECT * FROM tbl_contacts ORDER BY instrument;");
        rs = db.ExecuteSelectStatement(sql.toString());
        try
        {
            while (rs.next())
            {
                inviteesModel.add(new InviteesModel(rs.getString("firstName"),
                        rs.getString("lastName"),
                        rs.getString("instrument"),
                        false,
                        rs.getString("email")));
            }
        } catch (Exception e)
        {
            log.log(Level.SEVERE, e.getMessage());
        }
        return inviteesModel;
    }

    public ArrayList getAllContacts()
    {
        ArrayList<ContactsModel> contactsModel = new ArrayList();
        sql = new StringBuilder("SELECT * FROM tbl_contacts;");
        rs = db.ExecuteSelectStatement(sql.toString());
        try
        {
            while (rs.next())
            {
                contactsModel.add(new ContactsModel(rs.getString("firstName"),
                        rs.getString("lastName"),
                        rs.getString("instrument"),
                        rs.getString("phone"),
                        rs.getString("email"),
                        rs.getString("notes")));
            }
        } catch (Exception e)
        {
            log.log(Level.SEVERE, e.getMessage());
        }
        return contactsModel;
    }

    public String getContactForPresenceTD(int contactID)
    {
        StringBuilder query = new StringBuilder("SELECT * FROM tbl_contacts WHERE id = ");
        StringBuilder contacts = new StringBuilder();
        query.append(contactID);
        query.append(" ;");
        ResultSet resultSet = db.ExecuteSelectStatement(query.toString());
        try
        {
            while (resultSet.next())
            {
                contacts.append("<td>");
                contacts.append(resultSet.getString("instrument"));
                contacts.append("</td><td>");
                contacts.append(resultSet.getString("firstName"));
                contacts.append(" ");
                contacts.append(resultSet.getString("lastName"));
                contacts.append("</td><td>");
                contacts.append(resultSet.getString("phone"));
                contacts.append("</td><td>");
                contacts.append(resultSet.getString("email"));
                contacts.append("</td>");
            }
        } catch (Exception e)
        {
            contacts.append(e.getMessage());
            log.log(Level.SEVERE, e.getMessage());
        }
        return contacts.toString();
    }

    public boolean newContactSaved(String contactFirstName, String contactLastName, String contactInstrument, String contactPhone, String contactEmail, String contactNote)
    {
        try
        {
            sql = new StringBuilder("INSERT INTO tbl_contacts (instrument, firstName, lastName, email, phone, notes) VALUES ('");
            sql.append(contactInstrument);
            sql.append("','");
            sql.append(contactFirstName);
            sql.append("','");
            sql.append(contactLastName);
            sql.append("','");
            sql.append(contactEmail);
            sql.append("','");
            sql.append(contactPhone);
            sql.append("','");
            sql.append(contactNote);
            sql.append("');");
            db.ExecuteInsertStatement(sql.toString());
        } catch (Exception e)
        {
            log.log(Level.SEVERE, e.getMessage());
            return false;
        }
        return true;
    }

    public boolean ContactUpdated(ArrayList<ContactsModel> contactsModel)
    {
        ArrayList<ContactsModel> oldContacts = this.getAllContacts();
        try
        {
            for (int i = 0; i < contactsModel.size(); i++)
            {
                sql = new StringBuilder("UPDATE tbl_contacts SET instrument = '");
                sql.append(contactsModel.get(i).getInstrument());
                sql.append("', firstName = '");
                sql.append(contactsModel.get(i).getFirstName());
                sql.append("', lastName = '");
                sql.append(contactsModel.get(i).getLastName());
                sql.append("', email = '");
                sql.append(contactsModel.get(i).getEmail());
                sql.append("', phone = '");
                sql.append(contactsModel.get(i).getPhone());
                sql.append("', notes = '");
                sql.append(contactsModel.get(i).getNote().replace("'", "#!#"));
                sql.append("' WHERE ");
                sql.append("email = '");
                sql.append(oldContacts.get(i).getEmail());
                sql.append("';");
                db.ExecuteUpdateStatement(sql.toString());
            }
        } catch (Exception e)
        {
            log.log(Level.SEVERE, e.getMessage());
            return false;
        }
        return true;
    }

    public ArrayList<SubscriptionsModel> getSubscriptions(String projectName)
    {
        int projectID = Integer.parseInt(this.getProjectInfo("id", projectName));
        ArrayList<SubscriptionsModel> subscriptionsModel = new ArrayList();
        ArrayList contactIDs = new ArrayList();
        sql = new StringBuilder("SELECT * FROM tbl_dates WHERE projectID = ");
        sql.append(projectID);
        sql.append(" ;");
        rs = db.ExecuteSelectStatement(sql.toString());
        try
        {
            while (rs.next())
            {
                if (rs.getInt("contactID") != 0)
                {
                    contactIDs.add(rs.getInt("contactID"));
                }
            }
        } catch (Exception e)
        {
            log.log(Level.SEVERE, e.getMessage());
        }
        String names = "";
        for (Object contactID : contactIDs)
        {
            sql = new StringBuilder("SELECT * FROM tbl_contacts WHERE id = ");
            sql.append(contactID);
            sql.append(" ORDER BY instrument;");
            rs = db.ExecuteSelectStatement(sql.toString());
            try
            {
                while (rs.next())
                {
                    StringBuilder name = new StringBuilder(rs.getString("firstName"));
                    name.append(" ");
                    name.append(rs.getString("lastName"));
                    if (!names.contains(name.toString()))
                    {
                        subscriptionsModel.add(new SubscriptionsModel(name.toString(), rs.getString("instrument"), rs.getString("email")));
                        names += name;
                    }
                }
            } catch (Exception e)
            {
                log.log(Level.SEVERE, e.getMessage());
            }
        }
        return subscriptionsModel;
    }

    public String getPresenceAsString(String projectName)
    {
        // 1 in 1e loop Arraylist vullen met dates van contact 0.
        // 2 in 2e loop present ophalen waar contactid is contactID en date, date uit de Arraylist is. 
        int projectID = Integer.parseInt(this.getProjectInfo("id", projectName));
        StringBuilder presence = new StringBuilder("<table border=1><tr><td><b>Instrument</b></td><td><b>Naam</b></td><td><b>Telefoon</b></td><td><b>Email</b></td>");
        sql = new StringBuilder("SELECT * FROM tbl_dates WHERE projectID = ");
        sql.append(projectID);
        sql.append(" AND contactID = 0 ORDER BY date;");
        rs = db.ExecuteSelectStatement(sql.toString());
        try
        {
            //loop1
            while (rs.next())
            {
                presence.append("<td><b>");
                presence.append(rs.getString("date"));
                presence.append("<br/>");
                presence.append(rs.getString("location"));
                presence.append("</b></td>");
            }
            presence.append("</tr>");
            sql = new StringBuilder("SELECT * FROM tbl_dates WHERE projectID = ");
            sql.append(projectID);
            sql.append(" AND contactID != 0;");
            rs = db.ExecuteSelectStatement(sql.toString());
            //loop2
            while (rs.next())
            {
                if (!presence.toString().contains(this.getContactForPresenceTD(rs.getInt("contactID"))))
                {
                    presence.append("<tr>");
                    presence.append(this.getContactForPresenceTD(rs.getInt("contactID")));

                    StringBuilder s = new StringBuilder("SELECT present FROM tbl_dates WHERE contactID = ");
                    s.append(rs.getInt("contactID"));
                    s.append(" AND projectID = ");
                    s.append(projectID);
                    s.append(" ;");
                    ResultSet r = db.ExecuteSelectStatement(s.toString());
                    while (r.next())
                    {
                        presence.append("<td>");
                        if (r.getInt("present") == 0)
                        {
                            presence.append("<font color='red'>nee</font>");
                        } else
                        {
                            presence.append("ja");
                        }
                        presence.append("</td>");
                    }
                    presence.append("</tr>");
                }
            }
            presence.append("</table>");
        } catch (Exception e)
        {
            log.log(Level.SEVERE, e.getMessage());
        }
        return presence.toString();
    }

    public ArrayList<AbsenceModel> getPresence(String projectName)
    {
        int projectID = Integer.parseInt(this.getProjectInfo("id", projectName));
        ArrayList<AbsenceModel> absenceModel = new ArrayList();
        ArrayList contactIDs = new ArrayList();
        ArrayList dates = new ArrayList();
        ArrayList times = new ArrayList();
        ArrayList comments = new ArrayList();
        sql = new StringBuilder("SELECT * FROM tbl_dates WHERE projectID = ");
        sql.append(projectID);
        sql.append(" AND present = 'true' ORDER BY date;");
        rs = db.ExecuteSelectStatement(sql.toString());
        try
        {
            while (rs.next())
            {
                if (rs.getInt("contactID") != 0)
                {
                    contactIDs.add(rs.getInt("contactID"));
                    dates.add(rs.getString("date"));
                    times.add(rs.getString("startTime") + "-" + rs.getString("endTime"));
                    comments.add(rs.getString("commentsByContact"));
                }
            }
        } catch (Exception e)
        {
            log.log(Level.SEVERE, e.getMessage());
        }
        for (int i = 0; i < contactIDs.size(); i++)
        {
            sql = new StringBuilder("SELECT * FROM tbl_contacts WHERE id = ");
            sql.append(contactIDs.get(i));
            sql.append(" ;");
            rs = db.ExecuteSelectStatement(sql.toString());
            try
            {
                while (rs.next())
                {
                    StringBuilder name = new StringBuilder(rs.getString("firstName"));
                    name.append(" ");
                    name.append(rs.getString("lastName"));
                    absenceModel.add(new AbsenceModel(dates.get(i).toString(),
                            times.get(i).toString(),
                            name.toString(),
                            rs.getString("instrument"),
                            comments.get(i).toString()));
                }
            } catch (Exception e)
            {
                log.log(Level.SEVERE, e.getMessage());
            }
        }
        db.Disconnect();
        return absenceModel;
    }

    public ArrayList<AbsenceModel> getAbsence(String projectName)
    {
        int projectID = Integer.parseInt(this.getProjectInfo("id", projectName));
        ArrayList<AbsenceModel> absenceModel = new ArrayList();
        ArrayList contactIDs = new ArrayList();
        ArrayList dates = new ArrayList();
        ArrayList times = new ArrayList();
        ArrayList comments = new ArrayList();
        sql = new StringBuilder("SELECT * FROM tbl_dates WHERE projectID = ");
        sql.append(projectID);
        sql.append(" AND present = 'false' ORDER BY date;");
        rs = db.ExecuteSelectStatement(sql.toString());
        try
        {
            while (rs.next())
            {
                if (rs.getInt("contactID") != 0)
                {
                    contactIDs.add(rs.getInt("contactID"));
                    dates.add(rs.getString("date"));
                    times.add(rs.getString("startTime") + "-" + rs.getString("endTime"));
                    comments.add(rs.getString("commentsByContact"));
                }
            }
        } catch (Exception e)
        {
            log.log(Level.SEVERE, e.getMessage());
        }
        for (int i = 0; i < contactIDs.size(); i++)
        {
            sql = new StringBuilder("SELECT * FROM tbl_contacts WHERE id = ");
            sql.append(contactIDs.get(i));
            sql.append(" ;");
            rs = db.ExecuteSelectStatement(sql.toString());
            try
            {
                while (rs.next())
                {
                    StringBuilder name = new StringBuilder(rs.getString("firstName"));
                    name.append(" ");
                    name.append(rs.getString("lastName"));
                    absenceModel.add(new AbsenceModel(dates.get(i).toString(),
                            times.get(i).toString(),
                            name.toString(),
                            rs.getString("instrument"),
                            comments.get(i).toString()));
                }
            } catch (Exception e)
            {
                log.log(Level.SEVERE, e.getMessage());
            }
        }
        db.Disconnect();
        return absenceModel;
    }

    public boolean isAdminUser(String userName, String password)
    {
        //Moet nog , heeft geen prioriteit
        //sql = new StringBuilder("SELECT * FROM tbl_contacts WHERE ")
        return false;
    }

    public void saveSelectedProject(String projectStartDate,
            String projectEndDate,
            String projectRepertoire,
            boolean active,
            int requiredPresence,
            String selectedProjectName) throws Exception
    {
        sql = new StringBuilder("UPDATE tbl_projects SET projectStartDate = '");
        sql.append(projectStartDate);
        sql.append("', projectEndDate = '");
        sql.append(projectEndDate);
        sql.append("', projectRepertoire = '");
        sql.append(projectRepertoire.replace("'", "`"));
        sql.append("', active = '");
        sql.append(active);
        sql.append("', requiredPresence = ");
        sql.append(requiredPresence);
        sql.append(" WHERE projectName = '");
        sql.append(selectedProjectName.replace("'", "`"));
        sql.append("';");
        db.ExecuteUpdateStatement(sql.toString());

    }

    public AdminModel getAdminInfo(String function)
    {
        AdminModel buf = new AdminModel(null, null, null, null);
        sql = new StringBuilder("SELECT * FROM tbl_adminusers WHERE function = '");
        sql.append(function);
        sql.append("';");
        rs = db.ExecuteSelectStatement(sql.toString());
        try
        {
            while (rs.next())
            {
                buf = new AdminModel(rs.getString("userName"),
                        rs.getString("email"),
                        rs.getString("password"),
                        rs.getString("function"));
            }

        } catch (Exception e)
        {
            log.log(Level.SEVERE, e.getMessage());
        }
        return buf;
    }

    public void saveTextLabel(String name, String value) throws Exception
    {
        sql = new StringBuilder("UPDATE tbl_textlabels SET value = '");
        sql.append(value.replace("'", "`"));
        sql.append("' WHERE name = '");
        sql.append(name);
        sql.append("';");
        db.ExecuteUpdateStatement(sql.toString());
        db.Disconnect();
    }

    public void saveAdminMail(AdminModel scModel, AdminModel bcModel) throws Exception
    {
        sql = new StringBuilder("UPDATE tbl_adminusers SET email = '");
        sql.append(scModel.getEmail());
        sql.append("', password = '");
        sql.append(scModel.getPassword());
        sql.append("' WHERE userName = 'vuko.strijkerschef';");
        db.ExecuteUpdateStatement(sql.toString());

        sql = new StringBuilder("UPDATE tbl_adminusers SET email = '");
        sql.append(bcModel.getEmail());
        sql.append("', password = '");
        sql.append(bcModel.getPassword());
        sql.append("' WHERE userName = 'vuko.blazerschef';");
        db.ExecuteUpdateStatement(sql.toString());

        db.Disconnect();
    }

    public void removeContact(ContactsModel c) throws Exception
    {
        sql = new StringBuilder("DELETE FROM tbl_contacts WHERE firstName = '");
        sql.append(c.getFirstName());
        sql.append("' AND lastName = '");
        sql.append(c.getLastName());
        sql.append("' AND email = '");
        sql.append(c.getEmail());
        sql.append("' AND instrument = '");
        sql.append(c.getInstrument());
        sql.append("';");
        db.ExecuteUpdateStatement(sql.toString());
        db.Disconnect();
    }

    public void saveExistingContact(int contactID, ContactsModel c) throws Exception
    {
        sql = new StringBuilder("UPDATE tbl_contacts SET instrument = '");
        sql.append(c.getInstrument());
        sql.append("', firstName = '");
        sql.append(c.getFirstName().replace("'", "`"));
        sql.append("', lastName = '");
        sql.append(c.getLastName().replace("'", "`"));
        sql.append("', email = '");
        sql.append(c.getEmail());
        sql.append("', phone = '");
        sql.append(c.getPhone());
        sql.append("', notes = '");
        sql.append(c.getNote().replace("'", "`"));
        sql.append("' WHERE id = ");
        sql.append(contactID);
        sql.append(";");
        db.ExecuteUpdateStatement(sql.toString());
        db.Disconnect();
    }

    public void saveNewProject(ProjectModel pm) throws Exception
    {
        sql = new StringBuilder("INSERT INTO tbl_projects ");
        sql.append("(projectName, projectStartDate, projectEndDate, projectRepertoire, active, requiredPresence) ");
        sql.append("VALUES ('");
        sql.append(pm.getProjectName().replace("'", "`"));
        sql.append("', '");
        sql.append(pm.getProjectStartDate());
        sql.append("', '");
        sql.append(pm.getProjectEndDate());
        sql.append("', '");
        sql.append(pm.getProjectRepertoire().replace("'", "`"));
        sql.append("', 'true'");
        sql.append(", ");
        sql.append(pm.getRequiredPresence());
        sql.append(");");
        db.ExecuteInsertStatement(sql.toString());
    }

    public void saveNewDate(DatesModel d) throws Exception
    {
        sql = new StringBuilder("INSERT INTO tbl_dates ");
        sql.append("(projectID, contactID, date, startTime, endTime, present, commentsByContact, location, dateInfo) ");
        sql.append("VALUES (");
        sql.append(d.getProjectID());
        sql.append(", ");
        sql.append(d.getContactID());
        sql.append(", '");
        sql.append(d.getDate());
        sql.append("', '");
        sql.append(d.getStartTime());
        sql.append("', '");
        sql.append(d.getEndTime());
        sql.append("', '");
        sql.append(d.isPresent());
        sql.append("', '");
        sql.append(d.getCommentsByContact());
        sql.append("', '");
        sql.append(d.getLocation().replace("'", "`"));
        sql.append("', '");
        sql.append(d.getDateInfo().replace("'", "`"));
        sql.append("');");
        db.ExecuteInsertStatement(sql.toString());
    }

    public void delDate(DatesModel d) throws Exception
    {
        sql = new StringBuilder("DELETE FROM tbl_dates WHERE projectID = ");
        sql.append(d.getProjectID());
        sql.append(" AND date = '");
        sql.append(d.getDate());
        sql.append("' AND startTime = '");
        sql.append(d.getStartTime());
        sql.append("' AND endTime = '");
        sql.append(d.getEndTime());
        sql.append("' AND location = '");
        sql.append(d.getLocation());
        sql.append("';");
        db.ExecuteUpdateStatement(sql.toString());
    }

    public void updateProjectDates(DatesModel d) throws Exception
    {
        sql = new StringBuilder("UPDATE tbl_dates SET location = '");
        sql.append(d.getLocation());
        sql.append("' WHERE startTime = '");
        sql.append(d.getStartTime());
        sql.append("' AND endTime = '");
        sql.append(d.getEndTime());
        sql.append("' AND date = '");
        sql.append(d.getDate());
        sql.append("';");
        db.ExecuteUpdateStatement(sql.toString());
    }

}
